package reports.bestsellers;

import reports.ReportUtilities;

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.io.IOException;
import java.io.FileOutputStream;
import java.io.FileNotFoundException;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Created by IntelliJ IDEA.
 * User: raj.chhabra
 * Date: Jan 15, 2011
 * Time: 8:36:01 PM
 * To change this template use File | Settings | File Templates.
 */
public class BestSellersByStore extends ReportUtilities {

    private static final String[] titles = {"Store Code","Location Code","Vendor", "VSN","Item Code","Description","Total Sales"};
    private static final String[] values = {"store_cd","loc_cd","ve_cd","vsn","itm_cd","des","totalSales"};
    private static final String reportTitle = "Best Sellers Report By Location";


    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        BestSellersByStore report = new BestSellersByStore();
            
        Connection conn = report.getDBConnection(args[0]);
        report.fileName = args[1];

        String query = "select t1.*, itm.ve_cd,itm.vsn,itm.des from itm, " +
                "(select so_ln.store_cd,so_ln.loc_cd,so_ln.itm_cd, TO_CHAR(sum(so_ln.qty*so_ln.unit_prc),'$99999.99') as totalsales from so, so_ln " +
                "where so.stat_cd in ('O','F') " +
                "and so_ln.store_cd is not null " +
                "and so_ln.picked='F' " +
                "and SO_LN.VOID_FLAG='N' " +
                "and so.del_doc_num=so_ln.del_doc_num " +
                "and so_wr_dt between ('01-Jan-2010') and ('31-DEC-2010') " +
                "group by SO_LN.STORE_CD,SO_LN.LOC_CD,so_ln.itm_cd) t1 " +
                "where t1.itm_cd = itm.itm_cd " +
                "order by t1.STORE_CD,t1.loc_cd,t1.totalsales desc";

        Statement stmt = conn.createStatement();
        System.out.println("calling executQuery()");
        ResultSet rs = stmt.executeQuery(query);
        System.out.println("finished executQuery()");
        try {
            System.out.println("calling createSpreadsheet(rs)");
            report.createSpreadsheet(rs);
            System.out.println("finished createSpreadsheet(rs)");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            rs.close();
            stmt.close();
            conn.close();
        }
    }


    public void createSpreadsheet(ResultSet rs) throws SQLException, IOException {

        Workbook wb = new HSSFWorkbook();
        Map<String, CellStyle> styles = createStyles(wb);
        Sheet sheet = null;
        String currentStoreCode = "";
        String currentLocationCode = "";
        String storeCode = "";
        String locationCode = "";
        int rownum = 0;
        Row row;
        Cell cell;

        while (rs.next()){
            currentStoreCode = rs.getString("store_cd");
            currentLocationCode = rs.getString("loc_cd");

            if (!currentStoreCode.equalsIgnoreCase(storeCode) ||
                    (currentStoreCode.equalsIgnoreCase(storeCode) && !currentLocationCode.equalsIgnoreCase(locationCode))){
                sheet = createNewSheet((HSSFWorkbook)wb, currentStoreCode + " " + currentLocationCode, titles, reportTitle, styles);
                storeCode = currentStoreCode;
                locationCode = currentLocationCode;
                rownum = 2;
            }

            int j=0;
            row = sheet.createRow(rownum);

            for (String value:values) {
                cell = row.createCell(j);
                cell.setCellValue(rs.getString(value));
                if (value.equals("ve_cd"))
                    cell.setCellStyle(styles.get("cell_normal_center"));
                else
                    cell.setCellStyle(styles.get("cell_normal"));

                j++;
            }
            rownum++;
        }

        for (int x=0; x<wb.getNumberOfSheets();x++){
            Sheet s = wb.getSheetAt(x);
            for (int k=0; k<12; k++){
                s.autoSizeColumn((short)k);
            }
        }

        // Write the output to a file
        //String file = "CustomerReportCAN.xls";
        String file = fileName;
        if(wb instanceof XSSFWorkbook) file += "x";
        FileOutputStream out = new FileOutputStream(file);
        wb.write(out);
        out.close();
    }

    public void createSpreadsheet() throws SQLException, IOException {
        //To change body of implemented methods use File | Settings | File Templates.
    }
}
